# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
文件：inital_data_hipac
海拍客项目账务测试数据初始化:
1.授信成功数据、2.借款成功数据
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
import pymysql
import xlrd
from xlrd import xldate_as_tuple
from datetime import datetime
from pycacho.cachobase.file_deal import get_line

from pycacho.cachobase.logger import Logger
logger = Logger("inital_data_hipac").get_log()


class initalData():

    def __init__(self,database):
        self.db = pymysql.connect(
            host="10.16.20.182",      # 测试环境
            user="dev",               # 测试：dev   开发：root
            password="dev",           # 测试：dev   开发：123456
            port=3306,
            use_unicode=True,
            charset="utf8",
            database=database)        # hipac，account
        self.cursor = self.db.cursor()


    # name-数据库表名、type-数据库操作、stop_word-退出执行
    def deal_mysql(self,name,type,stop_word):
        file = 'F:\PythonProject\msydwork\\test\\' + name + '.sql'
        print("开始进行数据库：",name,"数据初始化：",type)
        data = get_line(file, type,stop_word)
        for sql in data:
            try:
                self.cursor.execute(sql)
                self.db.commit()
                print(sql)
            except:
                logger.info("异常......")

    def inital_data(self):
        if sys_code=='pethome':
            database = initalData('account')
            database.deal_mysql('account','delete','-delete')
            database.deal_mysql('account','insert','授信通过-'+sys_code)  #
            database.deal_mysql('account','update','授信通过-update')  #
            database = initalData('pay')
            database.deal_mysql('pay','delete','-')
            database = initalData(sys_code)
            database.deal_mysql(sys_code,'delete','授信通过-delete')         # delete、insert、update
            database.deal_mysql(sys_code,'insert','授信通过-insert')         # delete、insert、update
            database.deal_mysql(sys_code,'update','规则引擎-update')           # delete、insert、update
        if sys_code=='hipac':
            database = initalData('account')
            database.deal_mysql('account','delete','-delete')
            database.deal_mysql('account','insert','授信通过-'+sys_code)
            database = initalData('hipac')
            database.deal_mysql('hipac','delete','跳出')
            database = initalData('pay')
            database.deal_mysql('pay','delete','-')
        self.db.close()

    def loan_info(self,id, account_no, loan_no, ext_loan_no, loan_time,local_loan_time, fee_start_time, loan_amount, loan_remains,loan_period,loan_period_type,loan_expired_date,pay_status,pay_time,wired_file,create_time,update_time):
            sql = "INSERT INTO loan_info VALUES ('%s', '%s', 'HIPAC', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', 1, 0, 1, 0, '%s', '%s', '%s', 0, 0.00, 0, 0, 0.00, 0, '%s', '%s')" % \
                  (id, account_no, loan_no, ext_loan_no, loan_time,local_loan_time, fee_start_time, loan_amount, loan_remains,loan_period,loan_period_type,loan_expired_date,pay_status,pay_time,wired_file,create_time,update_time)
            try:
                self.cursor.execute(sql)
            except Exception as e:
                # 发生错误时回滚
                self.db.rollback()
                print(str(e))
            else:
                self.db.commit()  # 事务提交
                print(loan_no + '插入信息到pro_hb_loan成功！')

    def get_data(self,file):
        data = xlrd.open_workbook(file)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            insert_status = table.cell_value(i, 0)    # 第1列的产品类别
            if insert_status == 'S':
                account_no = table.cell_value(i, 3)      # 第4-D列的 账户编号
                product = table.cell_value(i, 4)             #
                loan_no = table.cell_value(i, 5)
                pay_status = int(table.cell_value(i, 6))
                amount = table.cell_value(i, 7)
                loan_date = datetime(*xldate_as_tuple(table.cell_value(i, 8), 0)).strftime('%Y-%m-%d') + ' 15:25:34'
                cur_date = datetime(*xldate_as_tuple(table.cell_value(i, 9), 0)).strftime('%Y-%m-%d') + ' 15:25:34'
                pay_date = datetime(*xldate_as_tuple(table.cell_value(i, 10), 0)).strftime('%Y-%m-%d') + ' 15:25:34'
                row.append(account_no)
                row.append(product)
                row.append(loan_no)
                row.append(pay_status)
                row.append(amount)
                row.append(loan_date)
                row.append(cur_date)
                row.append(pay_date)

                cellData.append(row)
        #print(cellData)
        return cellData

    def main(self):
        data = self.get_data('E:\zenglingwei\\test\\hipac.xls')
        print(data)


if __name__ == '__main__':
    sys_codes = ['pethome', 'hipac']
    sys_code = sys_codes[1]
    database = initalData(sys_code)
    database.inital_data()
    # database.main()
